﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Common.Dao.EsDao;
using Common.Extension;
using Common.Model;
using Common.util;
using Dapper;
using log4net;
using MoreLinq;
using MySql.Data.MySqlClient;

namespace Common.Dao
{
    public class CompanyNoticeDao
    {
        private static readonly ILog Log = LogManager.GetLogger(typeof(CompanyNoticeDao));
        //保存公告
        public static void BatchSaveNotices(List<CompanyNotice> news)
        {
            if (news.Count == 0)
            {
                return;
            }

            CompanyNoticeEsDao.SaveCompanyNotices(news);
            int batchSize = 10000;
            var batchData = news.Batch(batchSize);
            foreach (var data in batchData)
            {
                var sql = new StringBuilder("insert ignore `company_notice`(`id`,`site_time`,`crawling_time`,`stock_code`,`title`,`adjunct_url`) VALUES ");
                bool first = true;
                foreach (CompanyNotice model in data)
                {
                    if (first)
                    {
                        first = false;
                    }
                    else
                    {
                        sql.Append(",");
                    }
                    if (null == model.Content)
                    {
                        model.Content = "";
                    }
                    sql.Append($"('{model.Id}','{model.SiteTime}','{model.CrawlingTime}','{model.StockCode}','{MySqlHelper.EscapeString(model.Title)}','{MySqlHelper.EscapeString(model.AdjunctUrl)}')");
                }

                Log.Info("sql = " + sql);

                using (var cnn = DbUtils.GetHotTopicConnection())
                {
                    var execute = cnn.Execute(sql.ToString());
                    Log.Info("insert COMPANY_NOTICES data count = " + execute);
                }
            }
        }

        public static void UpdateNoticesContent(List<CompanyNotice> news)
        {
            CompanyNoticeEsDao.SaveCompanyNotices(news);

            String sql =
                @"update `company_notice` SET content = @Content WHERE id = @Id";
            using (var conn = DbUtils.GetHotTopicConnection())
            {
                conn.InsertMultiple(sql, news);
            }
        }

        //获取最新一条公告
        public static CompanyNotice GetLastNotice()
        {
            string sql =
                "SELECT id,site_time AS SiteTime,crawling_time AS CrawlingTime,stock_code AS StockCode,title,adjunct_url AS AdjunctUrl FROM `company_notice` " +
                "ORDER BY site_time DESC LIMIT 1";

            using (var conn = DbUtils.GetHotTopicConnection())
            {
                var list = conn.Query<CompanyNotice>(sql).ToList();
                return list.Any() ? list.First() : null;
            }
        }


        //获取全部公告
        public static List<CompanyNotice> GetAll()
        {
            string sql =
                "SELECT id,site_time AS SiteTime,crawling_time AS CrawlingTime,stock_code AS StockCode,title,adjunct_url AS AdjunctUrl,content AS Content FROM `company_notice` " +
                "ORDER BY site_time";
            using (var conn = DbUtils.GetHotTopicConnection())
            {
                return conn.Query<CompanyNotice>(sql)?.ToList();
            }
        }

        //获取全部公告
        public static List<CompanyNotice> GetAll(int pageSize, int page)
        {
            string sql =
                "SELECT id,site_time AS SiteTime,crawling_time AS CrawlingTime,stock_code AS StockCode,title,adjunct_url AS AdjunctUrl,content AS Content FROM `company_notice` " +
                "ORDER BY site_time limit @offest,@size";
            using (var conn = DbUtils.GetHotTopicConnection())
            {
                return conn.Query<CompanyNotice>(sql, new
                {
                    offest = pageSize * (page),
                    size = pageSize
                })?.ToList();
            }
        }

        public static int Count()
        {
            string sql = "SELECT count(id) FROM `company_notice`";
            MySqlCommand cmd1 = new MySqlCommand(sql, DbUtils.GetHotTopicConnection());
            MySqlDataReader rdr1 = cmd1.ExecuteReader();
            int count = 0;
            while (rdr1.Read())
            {
                int.TryParse(rdr1[0].ToString(), out count);
            }
            return count;
        }
    }
}